Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# LOOP Statements

`LOOP` statements execute a sequence of statements multiple times. The LOOP and END LOOP keywords enclose the statements. PL/SQL provides four kinds of loop statements: basic loop, `WHILE` loop, `FOR` loop, and cursor `FOR` loop. For usage information, see "Controlling Loop Iterations: LOOP and EXIT Statements".

Syntax

loop statement ::=

Description of the illustration basic_loop_statement.gif

while loop statement ::=

Description of the illustration while_loop_statement.gif

for loop statement ::=

Description of the illustration for_loop_statement.gif

cursor for loop statement ::=

Description of the illustration cursor_for_loop_statement.gif

Keyword and Parameter Description

basic_loop_statement

A loop that executes an unlimited number of times. It encloses a sequence of statements between the keywords `LOOP` and `END` `LOOP`. With each iteration, the sequence of statements is executed, then control resumes at the top of the loop. An `EXIT`, `GOTO`, or `RAISE` statement branches out of the loop. A raised exception also ends the loop.

boolean_expression

An expression that returns the Boolean value `TRUE`, `FALSE`, or `NULL`. It is associated with a sequence of statements, which is executed only if the expression returns `TRUE`. For the syntax of `boolean_expression`, see "Expression Definition".

cursor_for_loop_statement

Issues a SQL query and loops through the rows in the result set. This is a convenient technique that makes processing a query as simple as reading lines of text in other programming languages.

A cursor `FOR` loop implicitly declares its loop index as a `%ROWTYPE` record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

cursor_name

An explicit cursor previously declared within the current scope. When the cursor `FOR` loop is entered, `cursor_name` cannot refer to a cursor already opened by an `OPEN` statement or an enclosing cursor `FOR` loop.

cursor_parameter_name

A variable declared as the formal parameter of a cursor. For the syntax of `cursor_parameter_declaration`, see "Cursor Declaration". A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be `IN` parameters.

for_loop_statement

Numeric `FOR_LOOP` loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords `FOR` and `LOOP`.

The range is evaluated when the `FOR` loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by `lower_bound..upper_bound`. After each iteration, the loop index is incremented.

index_name

An undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself; you cannot reference the index outside the loop.

The implicit declaration of `index_name` overrides any other declaration outside the loop. To refer to another variable with the same name, use a label. See Example 4-15, "Using a Label for Referencing Variables Outside a Loop".

Inside a loop, the index is treated like a constant: it can appear in expressions, but cannot be assigned a value.

label_name

An optional undeclared identifier that labels a loop. `label_name` must be enclosed by double angle brackets and must appear at the beginning of the loop. Optionally, `label_name` (not enclosed in angle brackets) can also appear at the end of the loop.

You can use `label_name` in an `EXIT` statement to exit the loop labelled by `label_name`. You can exit not only the current loop, but any enclosing loop.

You cannot reference the index of a `FOR` loop from a nested `FOR` loop if both indexes have the same name, unless the outer loop is labeled by `label_name` and you use dot notation. See Example 4-16, "Using Labels on Loops for Referencing".

lower_bound .. upper_bound

Expressions that return numbers. Otherwise, PL/SQL raises the predefined exception `VALUE_ERROR`. The expressions are evaluated only when the loop is first entered. The lower bound need not be 1, it can be a negative integer as in `FOR i IN -5..10`. The loop counter increment (or decrement) must be 1.

Internally, PL/SQL assigns the values of the bounds to temporary `PLS_INTEGER` variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a `PLS_INTEGER` is -2147483648 to 2147483647, represented in 32 bits. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment. See "PLS_INTEGER Datatype".

By default, the loop index is assigned the value of `lower_bound`. If that value is not greater than the value of `upper_bound`, the sequence of statements in the loop is executed, then the index is incremented. If the value of the index is still not greater than the value of `upper_bound`, the sequence of statements is executed again. This process repeats until the value of the index is greater than the value of `upper_bound`. At that point, the loop completes.

record_name

An implicitly declared record. The record has the same structure as a row retrieved by `cursor_name` or `select_statement`.

The record is defined only inside the loop. You cannot refer to its fields outside the loop. The implicit declaration of `record_name` overrides any other declaration outside the loop. You cannot refer to another record with the same name inside the loop unless you qualify the reference using a block label.

Fields in the record store column values from the implicitly fetched row. The fields have the same names and datatypes as their corresponding columns. To access field values, you use dot notation, as follows:

`record_name.field_name`

Select-items fetched from the `FOR` loop cursor must have simple names or, if they are expressions, must have aliases. In the following example, `wages` is an alias for the select item `salary+NVL(commission_pct,0)*1000`:

`CURSOR c1 IS SELECT employee_id,`
`salary + NVL(commission_pct,0) * 1000 wages FROM employees ...`

REVERSE

By default, iteration proceeds upward from the lower bound to the upper bound. If you use the keyword `REVERSE`, iteration proceeds downward from the upper bound to the lower bound. An example follows:

    BEGIN
      FOR i IN REVERSE 1..10 LOOP  -- i starts at 10, ends at 1
       DBMS_OUTPUT.PUT_LINE(i); -- statements here execute 10 times
      END LOOP;
    END;
    /

The loop index is assigned the value of `upper_bound`. If that value is not less than the value of `lower_bound`, the sequence of statements in the loop is executed, then the index is decremented. If the value of the index is still not less than the value of `lower_bound`, the sequence of statements is executed again. This process repeats until the value of the index is less than the value of `lower_bound`. At that point, the loop completes.

select_statement

A query associated with an internal cursor unavailable to you. Its syntax is like that of `select_into_statement` without the `INTO` clause. See "SELECT INTO Statement". PL/SQL automatically declares, opens, fetches from, and closes the internal cursor. Because `select_statement` is not an independent statement, the implicit cursor `SQL` does not apply to it.

while_loop_statement

The `WHILE-LOOP` statement associates a Boolean expression with a sequence of statements enclosed by the keywords `LOOP` and `END` `LOOP`. Before each iteration of the loop, the expression is evaluated. If the expression returns `TRUE`, the sequence of statements is executed, then control resumes at the top of the loop. If the expression returns `FALSE` or `NULL`, the loop is bypassed and control passes to the next statement.

Usage Notes

You can use the `EXIT` `WHEN` statement to exit any loop prematurely. If the Boolean expression in the `WHEN` clause returns `TRUE`, the loop is exited immediately.

When you exit a cursor `FOR` loop, the cursor is closed automatically even if you use an `EXIT` or `GOTO` statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.

Examples

For examples, see the following:

Example 4-18, "Using EXIT With a Label in a LOOP"
Example 6-10, "Fetching With a Cursor"
Example 6-13, "Fetching Bulk Data With a Cursor"

Related Topics

"Controlling Loop Iterations: LOOP and EXIT Statements"
"Cursor Declaration"
"EXIT Statement"
"FETCH Statement"
"OPEN Statement"